'''
Created on 19 de Mar de 2011

@author: Tiago Martins
Codigo gerado no IDE Eclipse usando a versao 2.7 do Python como interpretador
'''
# -*- coding: utf-8 -*-

import urllib2; # Abrir ligacoes para obter dados de um determinado url
import BeautifulSoup; # HTML/XML Parser de dados determinados
import re; # Import de funcoes para expressoes regulares
import sqlite3 # SQL Importer

# Obter <a href tags dentro de uma tag de outro tipo
def getCodesAndInfo(data, tag, groupIndex):
    codes = { };
    sData = BeautifulSoup.BeautifulSoup(data);
    sData = sData.findAll(tag);
    for tag in sData:
        if(str(tag.contents[0]).startswith("<a") == True):
            codes[getExpressionResults("(?<==)\d+", str(tag.contents[0]['href']), groupIndex)] = str(tag.contents[0].contents[0]).decode('utf-8');
    return codes;

                
def getExpressionResults(regex, data, groupIndex):
    tagCode = re.findall(regex, data); # Converte o conteudo da tag para string
    return tagCode[groupIndex];

# Chave do dicionario de dados = nome instituicao, valores = estabelecimentos
def mixInstCodes(inst):
    instCode = {};
    for item in inst.items():
            stabName = str(item[1]).partition(" -")[0]; # Token= '-'
            if(instCode.has_key(stabName) == False):
                instCode[stabName] = str(item[0]);
            else:
                instCode[stabName] = instCode[stabName] + ";" +str(item[0]);
    return instCode;

def splitString(string, token):
    return str(string).rsplit(token);
    
def getTeacherData(data):
    teacherList = { };
    teacherData = [ ];
    nameData = BeautifulSoup.BeautifulSoup(data);
    sData = BeautifulSoup.BeautifulSoup(data);
    sData = sData.findAll('tr');
    sData = sData[1:]; # O index 0 da variavel sData contem tags TH que nao interessam.
    for trTag in sData:
        tdList = trTag.findAll('td');
        teacherName = str(tdList[0].contents[0]).replace("&nbsp;", "").decode("utf-8").strip();
        if(len(teacherName) > 0):
            teacherData.append(teacherName);
            teacherData.append(str(tdList[1].contents[0]).decode("utf-8").strip());
            teacherData.append(str(tdList[2].contents[0]).decode("utf-8").strip());
            teacherData.append(str(tdList[3].contents[0]).decode("utf-8").strip());
            teacherData.append(str(tdList[4].contents[0]).decode("utf-8").strip());
            teacherData.append(getExpressionResults("(?<==)\d+", str(tdList[5].find('a')['href']), 0));
            print nameData;
            teacherData.append(nameData.findAll('h2')[1].contents[0]);
            teacherList[teacherData[5]] = teacherData;
            teacherData = [ ];
    return teacherList;

# Executa apenas um for para a cada list contendo um professor, inserir na tabela correspondente
def getTeacherFromDict(dataYear, tableName, tData, instCode):
    for teacherKey in tData.keys():
        insertDataInTable(dataYear, tableName, tData[teacherKey], instCode);
    
def createTables(tableName):
    dbCon = sqlite3.connect("C:\\Users\\apple\\Downloads\\sqlite\\rebides.db3");
    dbCursor = dbCon.cursor();
    dbQuery = "CREATE TABLE '{0}' (tAno, tNome, tGrau, tCurso, tCat, tReg, tCode, stabCode, stabName)".format(tableName);
    try:
        dbCursor.execute(dbQuery);
    except:
        easterEgg = None;
    dbCon.commit();
    dbCursor.close();

# Cria uma tabela destinada as instituicoes
def createInstTable():
    dbCon = sqlite3.connect("C:\\Users\\apple\\Downloads\\sqlite\\rebides.db3");
    dbCursor = dbCon.cursor();
    dbQuery = "CREATE TABLE 'Institutions' (tYear, instName, tStabs)";
    try:
        dbCursor.execute(dbQuery);
    except:
        easterEgg = None;
    dbCon.commit();
    dbCursor.close();    

def insertInstInfo(dInst, dStabs, year):
    dbCon = sqlite3.connect("C:\\Users\\apple\\Downloads\\sqlite\\rebides.db3");
    dbCursor = dbCon.cursor();
    dbQuery = "INSERT INTO 'Institutions' VALUES('{0}', '{1}', '{2}')".format(year, dInst, dStabs);
    dbCursor.execute(dbQuery);
    dbCon.commit();
    dbCursor.close();    
    
def insertDataInTable(dataYear, tableName, tData, instCode):
    dbCon = sqlite3.connect("C:\\Users\\apple\\Downloads\\sqlite\\rebides.db3");
    dbCursor = dbCon.cursor();
    _tName = tData[0]
    _tDegree = tData[1];
    _tCourse = str(tData[2]);
    _tCat = tData[3];
    _tReg = tData[4];
    _tCode = tData[5];
    _tStabName = tData[6];
    _tInstCode = instCode;
    dbQuery = 'INSERT INTO "{0}" VALUES("{1}", "{2}", "{3}", "{4}", "{5}", "{6}", "{7}", "{8}", "{9}")'.format(str(tableName.encode('utf-8')),
                                                                                                      dataYear,
                                                                                                      _tName,
                                                                                                      _tDegree,
                                                                                                      _tCourse.replace("\"", ""),
                                                                                                      _tCat,
                                                                                                      _tReg,
                                                                                                      _tCode,
                                                                                                      _tInstCode,
                                                                                                      _tStabName);
    try:
        dbCheck =  dbQuery = "SELECT COUNT(*) FROM '{0}' WHERE tAno='{1}' AND tCode='{2}'".format(str(tableName.encode('utf-8')), 
                                                                                                    dataYear, 
                                                                                                    _tCode);
        dbCursor.execute(dbCheck);   
        for row in dbCursor:
            rData = str(row).replace("(", "").replace(",)", "");
            if(int(rData) == 0):
                dbCursor.execute(dbQuery);
                print dbQuery;
            else:
                print "Exists.";
    except:
        print "Already exists.";
    dbCon.commit();
    dbCursor.close();
       

def getNumberOfTeachers(tableName, year):
    dbCon = sqlite3.connect("C:\\Users\\apple\\Downloads\\sqlite\\rebides.db3");
    dbCon.text_factory = str;
    dbCursor = dbCon.cursor();
    dbQuery = "SELECT * FROM '{0}' WHERE tAno='{1}'".format(str(tableName).encode("utf-8"), year);
    dbCursor.execute(dbQuery);
    nTeachers = len(dbCursor.fetchall());
    dbCon.commit();
    dbCursor.close();
    return nTeachers;

def getAllTeachers(year):
    nTeachers = 0;
    for tName in getDatabaseTables():
        nTeachers += getNumberOfTeachers(tName, year);
    return nTeachers;

def getDatabaseTables():
    tNames = [];
    dbCon = sqlite3.connect("C:\\Users\\apple\\Downloads\\sqlite\\rebides.db3");
    dbCursor = dbCon.cursor();
    dbQuery = "SELECT * FROM sqlite_master WHERE type='table'";
    dbCursor.execute(dbQuery);
    for tableName in dbCursor:
        tNames.append(tableName[1]);
    dbCon.commit();
    dbCursor.close();
    return tNames;

# Retorna o numero de individuos com base no estabalecimento onde ensina. [Opcional = grau de educacao]
def getTeacherFromStab(stabName, year, degree=None):
    stab = { };
    dbCon = sqlite3.connect("C:\\Users\\apple\\Downloads\\sqlite\\rebides.db3");
    dbCursor = dbCon.cursor();
    for table in getDatabaseTables():
        try:
            if(degree is None):
                dbQuery = "SELECT COUNT(*) FROM '{0}' WHERE stabName='{1}' AND tAno='{2}'".format(table, stabName, 2000);
            else:
                dbQuery = "SELECT COUNT(*) FROM '{0}' WHERE stabName='{1}' AND tAno='{2}' AND tGrau='{3}'".format(table, stabName, 2000, degree);
            dbCursor.execute(dbQuery);
            
            for row in dbCursor:
                rData = str(row).replace("(", "").replace(",)", "");
                if(int(rData) > 0):
                    stab[stabName] = rData;
                    break;
            dbCon.commit();
        except:
            easterEgg = None;
    dbCursor.close();
    return stab;

def getTeacherDegreePerYear(degree, year):
    total = 0;
    dbCon = sqlite3.connect("C:\\Users\\apple\\Downloads\\sqlite\\rebides.db3");
    dbCursor = dbCon.cursor();
    dbQuery = "SELECT * FROM sqlite_master WHERE type='table'";
    dbCursor.execute(dbQuery);
    for tableName in dbCursor:
        dbQuery = "SELECT COUNT(*) FROM '{0}' WHERE tGrau='{1}' AND tAno='{2}'".format(str(tableName[1]).encode("utf-8"), degree, year);
        dbTable = dbCon.cursor();
        dbTable.execute(dbQuery);
        for row in dbTable:
            total += int(str(row).replace("(", "").replace(",)", ""));
        dbTable.close();
    dbCon.commit();
    dbCursor.close();
    return total;

def getInstByYear(year):
    inst = [];
    dbCon = sqlite3.connect("C:\\Users\\apple\\Downloads\\sqlite\\rebides.db3");
    dbCursor = dbCon.cursor();
    
    
# Para cada instituicao e visto os estabelecimentos correspondentes  
def organizeInstitutions(dInst, dStab, year):   
    stabNames = "";
    for inst in dInst:
        for stab in str(dInst[inst]).split(";"):
            stabNames = stabNames.__add__(dStab[stab] + ";");
        insertInstInfo(inst, stabNames, year);
        stabNames = "";
        
def iterateStabs(data, year, iCode):
    for inst in data:
        createTables(str(inst).encode('utf-8'));
        codes = str(data[inst]).split(";");
        for sCode in codes:
            teacherData = urllib2.urlopen("http://www.rebides.oces.mctes.pt/Rebides0{0}/rebid_m2.asp?CodR={1}&CodP={2}".format(year
                                                                                                                               , iCode
                                                                                                                               , sCode)).read();
            teacherData = getTeacherData(teacherData);                                                                                            
            #getTeacherFromDict((2000 + year), inst, teacherData, sCode);
                                                                                                                            
            
                                                                                                        
    
createInstTable();
for year in range(6, 7):
    iH2Data = { };
    iLiData = { };
    urlData = urllib2.urlopen("http://www.rebides.oces.mctes.pt/Rebides0{0}/".format(year));
    mainCodes = getCodesAndInfo(urlData, 'h2', 0);
    for iCode in mainCodes:
        instData = urllib2.urlopen("http://www.rebides.oces.mctes.pt/Rebides0{0}/rebid_m1.asp?codr={1}".format(year, iCode)).read();
        iH2Data = getCodesAndInfo(instData, 'h2', 1);
        iLiData = getCodesAndInfo(instData, 'li', 1);
        mainData = dict(iH2Data, **iLiData);
        organizedData = mixInstCodes(mainData);
        #organizeInstitutions(organizedData, mainData, year);
        iterateStabs(organizedData, year, iCode);
        #mainData = { };
    
